package org.spark.example

import org.apache.spark.sql.{SQLContext, SparkSession}
import org.apache.spark.{SparkConf, SparkContext}

/**
  * @Author: chengjj
  * @Date: 2020-08-27
  */
object SparkSqlTest {
    def main(args: Array[String]): Unit = {
        val conf = new SparkConf();
        conf.setAppName("test").setMaster("local");
        val sc = new SparkContext(conf);
        val session = SparkSession.builder().appName("test").master("local").getOrCreate();
        val df = session.read.json("E:\\test\\spark\\test.json");

        df.createOrReplaceTempView("nature");
        session.catalog.listTables().show();
        df.show();

        df.printSchema();
        df.select("name").show();
        df.select(df("name"),  df("fdTenantId")+1).show();
        df.groupBy("name").count().show();
        df.filter(df("code") > "nr").show();
        df.filter(df("code") like "n*").show();
        df.select(df("name"));
        df.sqlContext.sql("select name, length(name), replace(name, '词', 'word')  from nature").show(30);
        df.sqlContext.sql("select count(*) from nature").show();
        val sdf = session.sql("select * from nature");
        sdf.show();

        val df1 = session.read.json("E:\\test\\spark\\test1.json");
        df1.createOrReplaceTempView("na");
        df1.show();
        df1.printSchema();
        df1.agg("extendProps" ->"avg").show();
        df1.agg("extendProps" ->"max").show();

        session.catalog.listTables().show();
        import session.implicits._

        val df3 = session.read.json("E:\\test\\spark\\test.json");

        val ds = df3.select("available", "code", "createTime", "fdId", "fdTenantId", "id","lastUpdate", "name").as[Nature];
        ds.createOrReplaceTempView("nature1")
        ds.sqlContext.sql("select * from nature1").show();
        session.sql("select * from nature1");


    }

    case class Nature(available:Boolean, code:String, createTime:Long, fdId:String, fdTenantId:String,id:String ,
                      lastUpdate:Long, name:String);

    case class ExtendProps(a:String)
}
